package mobi.weq.util.edinburgh;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;

import com.mysql.jdbc.Statement;

import mobi.weq.db.Database;
import mobi.weq.util.Util;

public class SetUp {
	public static void createDataBase() throws SQLException {
		/* create database */
		Connection conn = Database.getConnection();
		Statement s = (Statement) conn.createStatement();
		s.executeUpdate("drop schema if exists " + Database.NAME);
		s.executeUpdate("CREATE DATABASE " + Database.NAME);
		s.execute("use  " + Database.NAME);
		System.out.println("Database created");

		/* create user table */
		String q = "create table if not exists user(" + "id varchar(255) not null," + "username varchar(255) not null," + "display_name varchar(255),"
				+ "password varchar(255) not null," + "email varchar(255), " + "use_user_feedback bit," + "has_lung_problems bit,"
				+ "has_respiratory_problems bit," + "has_heart_problems bit," + "age_group enum('child', 'adult', 'senior','unspecified')," + "primary key(id)"
				+ ")";

		s.executeUpdate(q);
		System.out.println("User Table created");

		/* create user observation table */
		q = "create table if not exists uo_template(" + "id varchar(255) not null," + "toleranceLevel integer," + "latitude double null,"
				+ "longitude double null," + "timestamp datetime null," + "primary key(id)" + ")";

		s.executeUpdate(q);
		System.out.println("User Observation created");

		/* create sensor observation table */
		q = "create table if not exists so_template(" + "id varchar(255) not null," + "no2 double null," + "so2 double null," + "o3 double null,"
				+ "pm10 double null," + "pm2p5 double null," + "latitude double null," + "longitude double null," + "timestamp datetime null,"
				+ "primary key(id)" + ")";

		s.executeUpdate(q);
		System.out.println("Sensor Observation Table created");

		/* generate database */
		generateData();

		showTables(s);
		displaySize(s);
		s.close();
	}

	public static void showTables(Statement s) throws SQLException {
		String q = "Show tables";
		ResultSet results = s.executeQuery(q);
		System.out.println();
		System.out.println("----------------------------");
		System.out.println("Tables Created");
		System.out.println("----------------------------");
		while (results.next()) {
			System.out.println(results.getString(1));
		}
		System.out.println("----------------------------");
		System.out.println();
	}

	public static void displaySize(Statement s) throws SQLException {
		String q = "SELECT table_schema \"Data Base Name\", SUM( data_length + index_length) / 1024 / 1024 \"Data Base Size in MB\" FROM information_schema.TABLES GROUP BY table_schema";
		ResultSet results = s.executeQuery(q);
		while (results.next()) {
			if (results.getString(1).equals(Database.NAME)) {
				System.out.println();
				double size = results.getDouble(2);
				System.out.println("Total Size:" + size + " MB");
				System.out.println();
			}
		}
	}

	public static void generateData() throws SQLException {
		System.out.println("----------------------------");
		System.out.println("Generating Database");
		System.out.println("----------------------------");

		Date d = new Date();
		d = Util.decreaseDateByDay(d, -150);
		EdinburghDataGenerator.generateData(180, d);
	}

	public static void main(String[] args) throws SQLException {
		long t = new Date().getTime();
		createDataBase();
		System.out.println("Time Taken in Seconds: " + (new Date().getTime() - t) / 1000);
	}
}
